import xlsxwriter
import binascii
import xlwt
import xlrd


# 导出为.xlsx
# columns: tuple / rows: list(tuple)
def exportToXlsxFromTupleList(columns, rows):
    workbook = xlsxwriter.Workbook('result.xlsx')
    worksheet = workbook.add_worksheet()
    cell_format = workbook.add_format({'font_name': 'Arial'})

    index_columns = 0
    for column in columns:
        worksheet.write(0, index_columns, column, cell_format)
        index_columns = index_columns + 1

    index_rows = 0
    col_indexes = []
    len_rows = len(rows)
    tag = 0
    for row in rows:
        if len(col_indexes) == 0:
            col_indexes = range(len(row))
        for col_index in col_indexes:
            field = row[col_index]
            field = getWantedField(field)
            worksheet.write(index_rows + 1, col_index, field, cell_format)
        temp = round(index_rows / len_rows, 2)
        if temp > tag:
            tag = temp
            print("{0}%".format(tag * 100))
        index_rows = index_rows + 1

    print('creating result.xlsx...')
    workbook.close()


# 导出为.xls
def exportToXlsFromTupleList(columns, rows):
    workbook = xlwt.Workbook()
    worksheet = workbook.add_sheet('sheet1', cell_overwrite_ok=True)

    index_columns = 0
    for column in columns:
        worksheet.write(0, index_columns, column)
        index_columns = index_columns + 1

    index_rows = 0
    col_indexes = []
    len_rows = len(rows)
    tag = 0
    for row in rows:
        if len(col_indexes) == 0:
            col_indexes = range(len(row))
        for col_index in col_indexes:
            field = row[col_index]
            field = getWantedField(field)
            worksheet.write(index_rows + 1, col_index, field)
        temp = round(index_rows / len_rows, 2)
        if temp > tag:
            tag = temp
            print("{0}%".format(tag * 100))
        index_rows = index_rows + 1

    print('creating result.xls...')
    workbook.save('result.xls')


# 导出为.xlsx
# columns: tuple / rows: list(dict)
def exportToXlsxFromDictList(columns, rows):
    workbook = xlsxwriter.Workbook('result.xlsx')
    worksheet = workbook.add_worksheet()
    cell_format = workbook.add_format({'font_name': 'Arial'})

    index_columns = 0
    for column in columns:
        worksheet.write(0, index_columns, column, cell_format)
        index_columns = index_columns + 1

    index_rows = 0
    col_indexes = []
    len_rows = len(rows)
    tag = 0
    for row in rows:
        item = tuple(row.values())
        if len(col_indexes) == 0:
            col_indexes = range(len(item))
        for col_index in col_indexes:
            field = item[col_index]
            field = getWantedField(field)
            worksheet.write(index_rows + 1, col_index, field, cell_format)
        temp = round(index_rows / len_rows, 2)
        if temp > tag:
            tag = temp
            print("{0}%".format(tag * 100))
        index_rows = index_rows + 1

    print('creating result.xlsx...')
    workbook.close()


def readExcel(file_path, sheet_name=u'Sheet1', column_index=0):
    try:
        data = xlrd.open_workbook(file_path)

        if sheet_name == u'Sheet1':
            table = data.sheet_by_index(0)
        else:
            table = data.sheet_by_name(sheet_name)

        rows_count = table.nrows
        column_names = table.row_values(column_index)
        list = []
        for row_num in range(1, rows_count):
            row = table.row_values(row_num)
            if row:
                item = {}
                for i in range(len(column_names)):
                    item[column_names[i]] = row[i]
                list.append(item)
        return list
    except Exception as e:
        print((str(e)))


# 将字段值转化为正常可用的字段值
def getWantedField(field):
    try:
        if isinstance(field, str):
            # 一般处理中文
            field = field.encode('latin-1').decode('gbk')
        if isinstance(field, bytes):
            #! 处理16进制的值 如果不是16进制需要修改此处
            field = binascii.b2a_hex(field)
            field = '0x' + field.decode()
    finally:
        return field
